from datetime import datetime
from sql import sqlseverDB
from config import *
from tool import change_task_status,getTimeStr,get_shopee_rcode
import concurrent.futures as thddd
import requests,logging,re
import sys,time,json,traceback
taskid=int(sys.argv[1])
tms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
rs_task=tms.ExecQuerydict('select * from task where id=?',(taskid,))
task_info=rs_task[0]
rw_name=task_info['tName']
isMulti=task_info['isMulti']
last_tid=task_info['LastHouTaiID']

logger = logging.getLogger()
logger.setLevel(logging.INFO) 
log_path=f'D:/Web/Gtupdate/GTLOG/{rw_name}{getTimeStr()}.log'
file_handler = logging.FileHandler(log_path, mode='a', encoding='utf-8')
tms.ExecNoQuery('update task set logPath=? where id=?',(log_path,taskid))
console_handler = logging.StreamHandler()
console_handler.setLevel(logging.INFO)
formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
file_handler.setFormatter(formatter)
console_handler.setFormatter(formatter)
logger.addHandler(file_handler)
logger.addHandler(console_handler)


session=requests.session()
session.headers={
    'Content-Type': 'application/json',
    'Accept': 'application/json',
    'Authorization': DeepSeek_Auth
}
with open('Cache/CNTOYN.json','r',encoding='utf-8') as f:
    cntoyn_dict=json.load(f)

with open('Cache/CNTOEN.json','r',encoding='utf-8') as f:
    cntoen_dict=json.load(f)

def req_ppd(pitem,j):
    global cntoyn_dict,cntoen_dict
    if taskid in (52,):
        pid,tit=pitem
        sys_set='电商产品标题翻译助手'
        question=f'将英语标题:{tit} 询问成印尼语作为产品标题,要在印尼shopee上使用,只返回翻译的印尼标题'
    elif taskid == 17:
        pid,tit,attrs=pitem
        sys_set='Shopee产品标题和描述生成助手'
        if attrs:
            json_attrs=json.loads(attrs)
            attrs_str=';'.join([f"{attr['name']}:{attr['value']}" for attr in json_attrs if '品牌' not in attr['name']])
            question=f'请用下面的原始产品标题和原始产品属性，写一个印尼语的产品标题和一个印尼语产品描述,要在印尼shopee上使用，把产品的主要特征、材质、适用人群等重要信息写入标题，非重要信息写入产品描述;标题和描述中不要含有中文;不要出现其它平台的名称。新标题格式：产品词+ 核心关键词+ 功能说明+ 属性（可多属性）+ 尺寸/数量;返回格式 标题:...\n描述:...。原始产品标题：{tit}\n原始产品属性：{attrs_str}\n返回格式 标题:...\n描述:...'
        else:
            question=f'请用原始产品标题：{tit}，写一个印尼语的产品标题和一个印尼语产品描述,要在印尼shopee上使用，把产品的主要特征、材质、适用人群等重要信息写入标题，非重要信息写入产品描述。标题和描述中不要含有中文。不要出现其它平台的名称。\n返回格式,标题:...\n描述:...。'


    elif taskid ==18:
        pid,itemid=pitem
        skus=set()
        skus_dict={}
        
        rs_sku=tms.ExecQuery('select id,sku1,sku2 from S1688ProSKU where itemid=?',(itemid,))
        for sid,sku1,sku2 in rs_sku:
            if sku1 and not cntoyn_dict.get(sku1):
                skus.add(sku1)
            if sku2 and not cntoyn_dict.get(sku2):
                skus.add(sku2)
            skus_dict[str(sid)]={'sku1':sku1,'sku2':sku2}
            
        if len(skus)==0:
            return_skus=[]
            for sid,skucnddd in skus_dict.items():
                cn_sku1=skucnddd['sku1']
                yn_sku1=None
                yn_sku2=None
                if cn_sku1:
                    yn_sku1=cntoyn_dict[cn_sku1]
                cn_sku2=skucnddd['sku2']
                if cn_sku2:
                    yn_sku2=cntoyn_dict[cn_sku2]
                return_skus.append([sid,yn_sku1,yn_sku2]) 
            return [j,pid,return_skus]
        
        sku_str='|||'.join(skus)
        sys_set='Shopee产品SKU翻译助手'
        question=f'''将以下SKU翻译成印尼语，要求：
                    1. 仅返回翻译结果，用|||分隔，不要额外解释
                    2. 每个SKU必须完全去除中文，使用缩写（如4pc、Std、Hijau）
                    3. 每个SKU总长度（包括空格和符号）严格≤20字符
                    4. 如果超长，优先截断或使用更短单词,并缩写至字符串长度等于20
                    {sku_str}'''

    elif taskid ==19:
        pid,itemid=pitem
        skus=set()
        skus_dict={}
        
        rs_sku=tms.ExecQuery('select id,sku1,sku2 from S1688ProSKU where itemid=?',(itemid,))
        for sid,sku1,sku2 in rs_sku:
            if sku1 and not cntoen_dict.get(sku1):
                skus.add(sku1)
            if sku2 and not cntoen_dict.get(sku2):
                skus.add(sku2)
            skus_dict[str(sid)]={'sku1':sku1,'sku2':sku2}
            
        if len(skus)==0:
            return_skus=[]
            for sid,skucnddd in skus_dict.items():
                cn_sku1=skucnddd['sku1']
                yn_sku1=None
                yn_sku2=None
                if cn_sku1:
                    yn_sku1=cntoen_dict[cn_sku1]
                cn_sku2=skucnddd['sku2']
                if cn_sku2:
                    yn_sku2=cntoen_dict[cn_sku2]
                return_skus.append([sid,yn_sku1,yn_sku2]) 
            return [j,pid,return_skus]
        
        sku_str='|||'.join(skus)
        sys_set='Shopee产品SKU翻译助手'
        question=f'''将以下SKU翻译成英语，要求：
                    1. 仅返回翻译结果，用|||分隔，不要额外解释
                    2. 每个SKU必须完全去除中文，使用缩写
                    3. 每个SKU总长度（包括空格和符号）严格≤20字符
                    4. 如果超长，优先截断或使用更短单词,并缩写至字符串长度等于20
                    {sku_str}'''

    elif taskid == 20:
        pid,tit,attrs=pitem
        sys_set='Shopee产品标题和描述生成助手'
        if attrs:
            json_attrs=json.loads(attrs)
            attrs_str=';'.join([f"{attr['name']}:{attr['value']}" for attr in json_attrs if '品牌' not in attr['name']])
            question=f'请用下面的原始产品标题和原始产品属性，写一个泰语的产品标题和一个泰语产品描述,要在泰国shopee上使用，把产品的主要特征、材质、适用人群等重要信息写入标题，非重要信息写入产品描述;标题和描述都不要含有中文,标题和描述都不可出现其他平台名称以及容易引发虾皮平台敏感的词汇。新标题格式：产品词+ 核心关键词+ 功能说明+ 属性（可多属性）+ 尺寸/数量;原始产品标题：{tit}\n原始产品属性：{attrs_str}\n返回格式 标题:...\n描述:...'
        else:
            question=f'请用原始产品标题：{tit}，写一个泰语的产品标题和一个泰语产品描述。要在泰国shopee上使用，把产品的主要特征、材质、适用人群等重要信息写入标题，非重要信息写入产品描述。标题和描述中不要含有中文。不要出现其它平台的名称。\n返回格式,标题:...\n描述:...'

    elif taskid == 38:
        pid,tit,attrs=pitem
        sys_set='Shopee产品标题和描述生成助手'
        if attrs:
            json_attrs=json.loads(attrs)
            attrs_str=';'.join([f"{attr['name']}:{attr['value']}" for attr in json_attrs if '品牌' not in attr['name']])
            question=f'请用下面的原始产品标题和原始产品属性，写一个马来语的产品标题和一个马来语产品描述,要在泰国shopee上使用，把产品的主要特征、材质、适用人群等重要信息写入标题，非重要信息写入产品描述;标题和描述都不要含有中文,标题和描述都不可出现其他平台名称以及容易引发虾皮平台敏感的词汇,标题需要同时使用马来语和英语呈现,标题不超过109个字符,产品名称必须放在标题首位,标题格式是“产品名称英语 【 产品名称马来语 】”，避免出现重复词汇，可以用“ - ”与其他描述衔接,标题不能笼统概括“多用途、多场景、多颜色尺寸”;原始产品标题：{tit}\n原始产品属性：{attrs_str}\n返回格式 标题:...\n描述:...'
        else:
            question=f'请用原始产品标题：{tit}，写一个马来语的产品标题和一个马来语产品描述。要在泰国shopee上使用，把产品的主要特征、材质、适用人群等重要信息写入标题，非重要信息写入产品描述。标题和描述中不要含有中文。不要出现其它平台的名称。\n返回格式,标题:...\n描述:...'


    #print(question)
    model='deepseek-chat'
    
    url = "https://api.deepseek.com/chat/completions"
    payload = {
        "messages": [
            {
                "content": sys_set,
                "role": "system"
            },
            {
                "content": question,
                "role": "user"
            }
        ],
        "model": model,
        "frequency_penalty": 0,
        "max_tokens": 8192,
        "presence_penalty": 0,
        "response_format": {
            "type": "text"
        },
        "stop": None,
        "stream": False,
        "stream_options": None,
        "temperature": 1,
        "top_p": 1,
        "tools": None,
        "tool_choice": "none",
        "logprobs": False,
        "top_logprobs": None
    }
    
    try:
        res = session.post( url,json=payload,timeout=300)
        if res.status_code==200:
            js_data=res.json()
            cot=js_data['choices'][0]['message']['content']
            if taskid==18:
                cn_sku_list=sku_str.split('|||')
                yn_sku_list=cot.split('|||')
                for sjjj in range(len(cn_sku_list)):
                    cntoyn_dict[cn_sku_list[sjjj]]=yn_sku_list[sjjj].strip()
                    
                return_skus=[]
                for sid,skucnddd in skus_dict.items():
                    cn_sku1=skucnddd['sku1']
                    yn_sku1=None
                    yn_sku2=None
                    if cn_sku1:
                        yn_sku1=cntoyn_dict[cn_sku1]
                    cn_sku2=skucnddd['sku2']
                    if cn_sku2:
                        yn_sku2=cntoyn_dict[cn_sku2]
                    return_skus.append([sid,yn_sku1,yn_sku2]) 
                return [j,pid,return_skus]
            
            elif taskid==19:
                cn_sku_list=sku_str.split('|||')
                yn_sku_list=cot.split('|||')
                for sjjj in range(len(cn_sku_list)):
                    cntoen_dict[cn_sku_list[sjjj]]=yn_sku_list[sjjj].strip()
                    
                return_skus=[]
                for sid,skucnddd in skus_dict.items():
                    cn_sku1=skucnddd['sku1']
                    yn_sku1=None
                    yn_sku2=None
                    if cn_sku1:
                        yn_sku1=cntoen_dict[cn_sku1]
                    cn_sku2=skucnddd['sku2']
                    if cn_sku2:
                        yn_sku2=cntoen_dict[cn_sku2]
                    return_skus.append([sid,yn_sku1,yn_sku2]) 
                return [j,pid,return_skus]
            else:
                return [j,pid,cot]
        else:
            logging.info(f'第{j}个询问DeepSeek,状态码出错 => {res.status_code}')

    except Exception as e:
        traceback.print_exc()
        logging.info(f'第{j}个询问DeepSeek,请求出错 => {e}')


def main():
    global cntoyn_dict,cntoen_dict
    st_time=time.time()
    
    if taskid==52:
        rs=tms.ExecQuery('select id,pname from ShopeePHADPro where Y_1688price is not null and yntitle is null')
    elif taskid==17:
        sql_str_ppp='''select top 1000 id,pname,attributes from S1688Pro p
                        where is_yntit=0 
                        and is_cj=1 
                        and is_yn_ad=0 
                        and pname not like '%定制%'
                        AND NOT EXISTS (
                            SELECT 1
                            FROM Blacklist b
                            WHERE b.word_language = '中文'
                            AND p.pname LIKE '%' + b.word_blacklist + '%')
                        order by keword_gender
                    '''
        rs=tms.ExecQuery(sql_str_ppp)
    elif taskid==18:
        rs=tms.ExecQuery('select id,itemid from S1688Pro where is_yntit=1 and  is_yn_ad=0 and is_fy_sku=0')
        #rs=tms.ExecQuery("select id,itemid from S1688Pro where itemid='784272523068' ")


    
    elif taskid==19:
        rs=tms.ExecQuery('select id,itemid from S1688Pro where (is_mltit=1 or is_thtit=1) and is_fy_ensku=0')

    elif taskid==20:

        sql_str_ppp='''select top 1000 id,pname,attributes from S1688Pro p
                where is_thtit=0 
                and is_cj=1 
                and pname not like '%定制%'
                AND NOT EXISTS (
                    SELECT 1
                    FROM Blacklist b
                    WHERE b.word_language = '中文'
                    AND p.pname LIKE '%' + b.word_blacklist + '%')
                order by keword_gender
            '''
        rs=tms.ExecQuery(sql_str_ppp)
    
    elif taskid==38:

        sql_str_ppp='''select top 1000 id,pname,attributes from S1688Pro p
                where is_mltit=0 
                and is_cj=1 
                and pname not like '%定制%'
                AND NOT EXISTS (
                    SELECT 1
                    FROM Blacklist b
                    WHERE b.word_language = '中文'
                    AND p.pname LIKE '%' + b.word_blacklist + '%')
                AND NOT EXISTS (
                        SELECT 1 FROM ShopeeAdsMove m
                        WHERE m.to_country='马来西亚' and m.random_code=p.random_code) 
                order by keword_gender
            '''
        rs=tms.ExecQuery(sql_str_ppp)

    task_count=len(rs)
    logging.info(f'任务:《{rw_name}》,共{task_count}个item待询问,开始运行...')
    print(f'当前任务ID:{taskid}')
    with thddd.ThreadPoolExecutor(max_workers=10) as t:

        tasks=[]
        j=1
        for pitem in rs:
            tasks.append(t.submit(req_ppd,pitem,j))
            j+=1
        
        for t0 in thddd.as_completed(tasks):
            res=t0.result()

            if res is not None:

                res_j,res_pid,res_cot=res

                if taskid==52:
                    aff=tms.ExecNoQuery('update ShopeePHADPro set yntitle=? where id=?',(res_cot,res_pid))

                elif taskid==17:

                    aff=0
                    mat_yntit=re.search(r'标题:(.*)描述',res_cot,re.S)
                    mat_yndes=re.search(r'描述:(.*)',res_cot,re.S)

                    if mat_yntit and mat_yndes:
                        yntit_str,yndes_str=mat_yntit.group(1).strip(),mat_yndes.group(1).strip()
                        aff=tms.ExecNoQuery('update S1688Pro set yntitle=?,yndes=?,is_yntit=1 where id=?',(yntit_str,yndes_str,res_pid))

                elif taskid==18:
                    aff=0
                    aff_sku=0
                    for sid,ynsku1,ynsku2 in res_cot:
                        aff_sku+=tms.ExecNoQuery('update S1688ProSKU set ynsku1=?,ynsku2=? where id=?',(ynsku1,ynsku2,int(sid)))

                    if aff_sku>0:
                        aff=tms.ExecNoQuery('update S1688Pro set is_fy_sku=1 where id=?',(res_pid,))
                
                      
                elif taskid==19:
                    aff=0
                    aff_sku=0
                    for sid,ynsku1,ynsku2 in res_cot:
                        aff_sku+=tms.ExecNoQuery('update S1688ProSKU set ensku1=?,ensku2=? where id=?',(ynsku1,ynsku2,int(sid)))

                    if aff_sku>0:
                        aff=tms.ExecNoQuery('update S1688Pro set is_fy_ensku=1 where id=?',(res_pid,))
                
                elif taskid==20:

                    aff=0
                    mat_yntit=re.search(r'标题:(.*)描述',res_cot,re.S)
                    mat_yndes=re.search(r'描述:(.*)',res_cot,re.S)

                    if mat_yntit and mat_yndes:
                        yntit_str,yndes_str=mat_yntit.group(1).strip(),mat_yndes.group(1).strip()
                        aff=tms.ExecNoQuery('update S1688Pro set thtitle=?,thdes=?,is_thtit=1 where id=?',(yntit_str,yndes_str,res_pid))

                elif taskid==38:

                    aff=0
                    mat_yntit=re.search(r'标题:(.*)描述',res_cot,re.S)
                    mat_yndes=re.search(r'描述:(.*)',res_cot,re.S)

                    if mat_yntit and mat_yndes:
                        yntit_str,yndes_str=mat_yntit.group(1).strip(),mat_yndes.group(1).strip()
                        aff=tms.ExecNoQuery('update S1688Pro set mltitle=?,mldes=?,is_mltit=1 where id=?',(yntit_str,yndes_str,res_pid))

                if taskid in (18,19):
                    logging.info(f'任务 《{rw_name}》,第 {res_j} 个,pid:{res_pid},询问返回结果长度:{len(res_cot)},翻译sku:{aff_sku}个,更改状态:{aff}')
                else:
                    logging.info(f'任务 《{rw_name}》,第 {res_j} 个,pid:{res_pid},询问返回结果长度:{len(res_cot)},保存状态:{aff}')
            
            task_count-=1
            tms.ExecNoQuery('update [task] set tcount=? where [id]=?',(task_count,taskid))
            logging.info(f'任务 《{rw_name}》,剩余询问任务:{task_count}/{len(rs)}')
    ed_time=time.time()
    hs=(ed_time-st_time)/3600
    logging.info(f'任务 《{rw_name}》,询问完毕,耗时: {hs:.2f} 小时')

    if taskid==18:
        with open('Cache/CNTOYN.json','w',encoding='utf-8') as f:
            json.dump(cntoyn_dict,f,ensure_ascii=False)
    if taskid==19:
        with open('Cache/CNTOEN.json','w',encoding='utf-8') as f:
            json.dump(cntoen_dict,f,ensure_ascii=False)
    time.sleep(10)

    tms.ExecNoQuery('update [task] set state=0,tcount=0,LastHouTaiID=null where [id]=?',(taskid,))
    change_task_status(taskid,0)

if __name__=='__main__':

    main()



